﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.IO;

namespace Synchros
{
    public class SqlHelper
    {
        private static readonly int iTableMaxCdcCount = 1000;

        #region 变更捕获

        #region 配置捕获
        public static void ConfigureChangedDataCatch(List<SyncItem> lstSync, bool isEnable)
        {
            #region 临时变量
            string strServer = string.Empty;
            string strDatabase = string.Empty;
            string strUserID = string.Empty;
            string strPassword = string.Empty;

            string strGroup = string.Empty;
            string strTable = string.Empty;
            string strCon = string.Empty;
            #endregion

            #region 配置库的变更捕获语句
            StringBuilder stbDbase = new StringBuilder();
            if (isEnable)
            {
                // 启用捕获(ggz:不能重复开启,会报错)
                stbDbase.Append(" IF EXISTS(SELECT 1 FROM sys.databases WHERE IS_CDC_ENABLED=0 AND name=@SdName)");
                stbDbase.Append(" EXEC sys.SP_CDC_ENABLE_DB");
            }
            else
            {
                // 禁用捕获(ggz:不能重复开启,会报错)
                stbDbase.Append(" IF EXISTS(SELECT 1 FROM sys.databases WHERE IS_CDC_ENABLED=1 AND name=@SdName)");
                stbDbase.Append(" EXEC sys.SP_CDC_Disable_DB");
            }
            string strDbaseCDC = stbDbase.ToString();
            #endregion



            try
            {
                for (int iGroup = 0; iGroup < lstSync.Count; iGroup++)
                {
                    SyncItem syncitem = lstSync[iGroup];
                    DataTable dtSyncDb = syncitem.SyncDb;
                    DataTable dtSyncTb = syncitem.SyncTb;
                    DataRow drDbMain = null;
                    int iDbMain = -1;
                    string strDbMainConnection = string.Empty;

                    for (int iDbase = 0; iDbase < dtSyncDb.Rows.Count; iDbase++)
                    {
                        DataRow drSyncDb = dtSyncDb.Rows[iDbase];

                        #region 加载连接信息
                        strServer = drSyncDb["Server"].ToString();
                        strDatabase = drSyncDb["Database"].ToString();
                        // 得到用户名密码
                        XmlHelper.ConfigureConnectionInfo(strServer, ref strUserID, ref strPassword);
                        strCon = SqlHelper.GetConnectionString(strServer, strUserID, strPassword, strDatabase);
                        #endregion

                        #region 配置服务器(更多安全配置/OpenRowset&OpenDatasource/纤程)

                        string[] strConfigures = new string[3];
                        strConfigures[0] = " exec sp_configure 'show advanced options', 1 RECONFIGURE";//--开启更多安全配置
                        strConfigures[1] = " exec sp_configure 'Ad Hoc Distributed Queries', 1 RECONFIGURE";//--开启OPENDATASOURCE和OPENDATASET
                        strConfigures[2] = " sp_configure 'lightweight pooling', 0 RECONFIGURE";//--关闭纤程(需要重启,启动后捕获实效)
                        try
                        {
                            using (SqlConnection con = new SqlConnection(strCon))
                            {
                                SqlCommand cmd = new SqlCommand();
                                cmd.Connection = con;
                                foreach (string strConfigure in strConfigures)
                                {
                                    cmd.CommandText = strConfigure;
                                    con.Open();
                                    UP("正在配置:[{0}],[{1}] {2}...", syncitem.SgName, strServer, strConfigure);
                                    cmd.ExecuteNonQuery();
                                    UP("配置成功:[{0}],[{1}] {2}   ", syncitem.SgName, strServer, strConfigure);
                                    con.Close();
                                }
                            }
                        }
                        catch (Exception exDbase)
                        {
                            throw exDbase;
                        }

                        UP("配置成功:[{0}],[{1}]]对组件 'Ad Hoc Distributed Queries' 的 STATEMENT 'OpenRowset/OpenDatasource' 的访问", lstSync[iGroup].SgName, strServer);
                        /**/
                        #endregion

                        #region 配置库的变更捕获
                        try
                        {
                            using (SqlConnection con = new SqlConnection(strCon))
                            {
                                SqlCommand cmd = new SqlCommand();
                                cmd.Connection = con;
                                cmd.CommandText = strDbaseCDC;
                                cmd.Parameters.AddWithValue("@SdName", strDatabase);
                                con.Open();
                                UP("正在配置:[{0}],[{1}].[{2}]...", syncitem.SgName, strServer, strDatabase);
                                cmd.ExecuteNonQuery();
                                UP("配置成功:[{0}],[{1}].[{2}]   ", syncitem.SgName, strServer, strDatabase);
                                cmd.Dispose();
                                con.Close();
                            }
                        }
                        catch (Exception exDbase)
                        {
                            throw exDbase;
                        }
                        #endregion

                        #region 配置表的变更捕获
                        for (int iTable = 0; iTable < dtSyncTb.Rows.Count; iTable++)
                        {
                            DataRow drSyncTb = dtSyncTb.Rows[iTable];

                            #region 配置表的变更捕获语句
                            StringBuilder stbTableConfigure = new StringBuilder();
                            strTable = drSyncTb["StName"].ToString();
                            if (Convert.ToBoolean(drSyncDb["IsMain"]))
                            {
                                drDbMain = drSyncDb;//设置主数据库对象
                                iDbMain = iDbase;

                                #region 主数据库,开启当前表捕获,返回开启时间
                                stbTableConfigure.Append(" IF EXISTS(SELECT 1 FROM SYS.TABLES WHERE(is_tracked_by_cdc=0)AND(NAME=@StName) AND SCHEMA_ID=SCHEMA_ID('DBO'))");// 开启捕获作业(ggz:不能重复开启,会报错)
                                stbTableConfigure.Append(" BEGIN");
                                {
                                    //stbTableConfigure.Append(" DECLARE @top_index_name sysname");
                                    //stbTableConfigure.Append(" SELECT TOP 1 @top_index_name=name ");
                                    //stbTableConfigure.Append(" FROM sys.indexes ");
                                    //stbTableConfigure.Append(" WHERE name IS NOT NULL");
                                    //stbTableConfigure.Append(" AND is_unique=1");
                                    //stbTableConfigure.Append(" AND object_id=object_id(@StName)");
                                    //stbTableConfigure.Append(" ORDER BY is_primary_key DESC");/*先主键唯一后唯一唯一*/                                   


                                    stbTableConfigure.Append(" EXEC sys.sp_cdc_enable_table");
                                    stbTableConfigure.Append(" @source_schema ='dbo'");
                                    stbTableConfigure.Append(",@source_name=@StName");
                                    stbTableConfigure.Append(",@role_name=null");
                                    stbTableConfigure.Append(",@capture_instance=null");
                                    stbTableConfigure.Append(",@supports_net_changes=0");//default
                                    stbTableConfigure.Append(",@index_name=null");//null/@top_index_name
                                    stbTableConfigure.Append(",@captured_column_list=null");
                                    stbTableConfigure.Append(",@filegroup_name=default");
                                    stbTableConfigure.Append(",@allow_partition_switch=1");
                                    stbTableConfigure.Append(" SELECT CONVERT(VARCHAR(23),GETDATE(),121)");

                                }
                                stbTableConfigure.Append(" END");
                                #endregion
                            }
                            else
                            {
                                DataTable dtColumn = drSyncTb["Column"] as DataTable;//得到当前表的所有列
                                stbTableConfigure.Append(" IF EXISTS(SELECT 1 FROM SYS.TABLES WHERE(is_tracked_by_cdc=0)AND(NAME=@StName) AND SCHEMA_ID=SCHEMA_ID('DBO'))");// 开启捕获作业(ggz:不能重复开启,会报错)
                                stbTableConfigure.Append(" BEGIN");
                                {
                                    //让他们自己在数据库里判断吧，开启了，就不用初始化了。。。哈哈，就是每次重新开启捕获时，就初始同步

                                    #region 批量添加--这里要加条件限制
                                    StringBuilder stbInsert = new StringBuilder(); /*生成批量插入语句*/
                                    {
                                        string strMainServer = drDbMain["Server"].ToString();
                                        string strMainDatabase = drDbMain["Database"].ToString();
                                        string strMainUserID = string.Empty;
                                        string strMainPassword = string.Empty;
                                        XmlHelper.ConfigureConnectionInfo(strMainServer, ref strMainUserID, ref strMainPassword); // 得到用户名密码
                                        strDbMainConnection = GetConnectionString(strMainServer, strMainUserID, strMainPassword, strMainDatabase);//得到连接字符串

                                        //拼接有所有列列表的批量插入语句
                                        stbInsert.Append(" INSERT INTO dbo.[");
                                        stbInsert.Append(strTable);
                                        stbInsert.Append("] (");
                                        stbInsert.Append(GetColumnString(dtColumn));
                                        stbInsert.Append(")");
                                        stbInsert.Append(" SELECT * FROM OPENDATASOURCE('SQLOLEDB','Data Source=");
                                        stbInsert.Append(strMainServer);
                                        stbInsert.Append(";User ID=");
                                        stbInsert.Append(strUserID);
                                        stbInsert.Append(";Password=");
                                        stbInsert.Append(Cryptogram.Decrypt(strPassword));
                                        stbInsert.Append("').[");
                                        stbInsert.Append(strMainDatabase);
                                        stbInsert.Append("].dbo.[");
                                        stbInsert.Append(strTable);
                                        stbInsert.Append("]");
                                    }
                                    //无标识列
                                    if (string.IsNullOrEmpty(GetIdentity(dtColumn)))
                                    {
                                        stbTableConfigure.Append(stbInsert);
                                    }
                                    //有标识列
                                    else
                                    {
                                        //生成判断标识列批量插入语句
                                        stbTableConfigure.Append(" SET IDENTITY_INSERT dbo.[");
                                        stbTableConfigure.Append(strTable);
                                        stbTableConfigure.Append("] ON");
                                        stbTableConfigure.Append(stbInsert);
                                        stbTableConfigure.Append(" SET IDENTITY_INSERT dbo.[");
                                        stbTableConfigure.Append(strTable);
                                        stbTableConfigure.Append("] OFF");
                                    }
                                    // 返回值
                                    stbTableConfigure.Append(" SELECT CONVERT(VARCHAR(23),GETDATE(),121),@@ROWCOUNT");//"COUNT(*)FROM" stbTableConfigure.Append(strTable);
                                    
                                    #endregion

                                    #region 开启捕获
                                    stbTableConfigure.Append(" EXEC sys.sp_cdc_enable_table");
                                    stbTableConfigure.Append(" @source_schema ='dbo'");
                                    stbTableConfigure.Append(",@source_name=@StName");
                                    stbTableConfigure.Append(",@role_name=null");
                                    stbTableConfigure.Append(",@capture_instance=null");
                                    stbTableConfigure.Append(",@supports_net_changes=0");
                                    stbTableConfigure.Append(",@index_name=null");
                                    stbTableConfigure.Append(",@captured_column_list=null");
                                    stbTableConfigure.Append(",@filegroup_name=default");
                                    stbTableConfigure.Append(",@allow_partition_switch=1");
                                    #endregion
                                }
                                stbTableConfigure.Append(" END");
                            }

                            //    //禁用捕获作业(ggz:可重复禁用,不报错,但需要库的变更捕获开启)
                            //    stbTableConfigure.Append(" IF EXISTS(SELECT 1 FROM sys.databases WHERE IS_CDC_ENABLED=1 AND name=@SdName)");
                            //    stbTableConfigure.Append(" EXEC sys.sp_cdc_disable_table 'dbo',@StName,'ALL'");
                            #endregion

                            SqlConnection con = null;
                            SqlTransaction tran = null;
                            SqlCommand cmd = null;
                            try
                            {

                                cmd = new SqlCommand();
                                cmd.CommandText = stbTableConfigure.ToString();
                                cmd.Parameters.AddWithValue("@SdName", strDatabase);
                                cmd.Parameters.AddWithValue("@StName", strTable);
                                cmd.CommandTimeout = 600;

                                con = new SqlConnection(strCon);
                                con.Open();

                                tran = con.BeginTransaction();//定义一个事务的开始
                                cmd.Connection = con;
                                cmd.Transaction = tran;

                                UP("正在配置:[{0}],[{1}].[{2}].dbo.[{3}]...", lstSync[iGroup].SgName, strServer, strDatabase, strTable);
                                SqlDataReader drd = cmd.ExecuteReader();
                                UP("配置成功:[{0}],[{1}].[{2}].dbo.[{3}]   ", lstSync[iGroup].SgName, strServer, strDatabase, strTable);

                                if (drd.HasRows && drd.Read())
                                {
                                    if (Convert.ToBoolean(drSyncDb["IsMain"]))
                                    {
                                        drSyncTb["StTime"] = drd[0];
                                        // 保存配置
                                        XmlHelper.SaveSyncConfig(lstSync);
                                    }
                                    else
                                    {
                                        #region 更新同步当前库中目标LSN
                                        byte[] lsn = GetMapTimeToLsn(drd[0], strDbMainConnection);
                                        if (lsn != null)
                                        {
                                            SyncLine syncLine = new SyncLine(GetSyncLineSfp(syncitem, iDbase, iTable));
                                            syncLine.SynLsn(GetSyncName(syncitem, iDbMain, iTable), lsn, TimeSlot.Full);
                                            syncLine.ClrLsn(GetSyncName(syncitem, iDbMain, iTable), lsn, TimeSlot.Full);
                                            string strLsn = "0x" + BitConverter.ToString(lsn).Replace("-", "");//test
                                        }
                                        #endregion

                                        //提示信息
                                        SM("起始同步:[{0}],[{1}].[{2}].dbo.[{3}],同步:{4}条", lstSync[iGroup].SgName, strServer, strDatabase, strTable, drd[1]);
                                        //UP("起始同步:[{0}],[{1}].[{2}].dbo.[{3}],同步:{4}条", lstSync[iGroup].SgName, strServer, strDatabase, strTable, drd[1]);
                                    }
                                }
                                drd.Close();

                                // 提交事务
                                tran.Commit();
                            }
                            catch (Exception exDbase)
                            {
                                // 回滚事务
                                tran.Rollback();
                                throw exDbase;
                            }
                            finally
                            {
                                try
                                {
                                    cmd.Dispose();
                                    tran.Dispose();
                                    con.Close();
                                }
                                catch { }
                            }
                        }
                        #endregion
                    }
                }
            }
            catch (Exception exGroup)
            {
                throw exGroup;
            }
            finally
            {
                UP(null);
            }
        }
        #endregion

        #region 清理捕获
        public static void ClearCdc(SyncItem syncitem)
        {
            DataTable dtSyncTb = syncitem.SyncTb;
            DataTable dtSyncDb = syncitem.SyncDb;

            for (int iTable = 0; iTable < dtSyncTb.Rows.Count; iTable++)
            {
                string strTableName = syncitem.SyncTb.Rows[iTable]["StName"].ToString();
                DataTable dtColumn = syncitem.SyncTb.Rows[iTable]["Column"] as DataTable;

                for (int iCurDb = 0; iCurDb < dtSyncDb.Rows.Count; iCurDb++)
                {
                    DataRow drCurDb = dtSyncDb.Rows[iCurDb];

                    DataTable dtOutCdc = new DataTable();

                    SyncLine syncline = new SyncLine(GetSyncLineSfp(syncitem, iCurDb, iTable));//加载的我记录文件

                    for (int iRefDb = 0; iRefDb < dtSyncDb.Rows.Count; iRefDb++)
                    {
                        if (iCurDb == iRefDb) continue;

                        DataRow drRefDb = dtSyncDb.Rows[iRefDb];

                        TimeSlot tsRef = syncline[GetSyncName(syncitem, iRefDb, iTable)];

                        if (TimeSlot.IsEmpty(tsRef.Max_LSN)) continue;

                        #region 拼接查询
                        SqlCommand cmd = new SqlCommand();
                        StringBuilder stbSelect = new StringBuilder();
                        {
                            stbSelect.Append(" SELECT __$ExecuteTime=sys.fn_cdc_map_lsn_to_time(__$start_lsn)");
                            stbSelect.Append(",__$SyncDbIndex=");stbSelect.Append(iRefDb);                            
                            stbSelect.Append(",*");
                            stbSelect.Append(" FROM ");stbSelect.Append(NSCDC(strTableName));
                            stbSelect.Append(" WHERE (__$start_lsn>@Min_LSN AND __$start_lsn<@Max_LSN)");
                            stbSelect.Append(" OR (__$start_lsn=@Min_LSN AND __$seqval> @Min_SEQ)");
                            stbSelect.Append(" OR (__$start_lsn=@Max_LSN AND __$seqval<=@Max_SEQ)");
                            stbSelect.Append(" ORDER BY __$start_lsn,__$seqval,__$operation");




                            cmd.Parameters.AddWithValue("@Max_LSN", tsRef.Max_LSN);
                            cmd.Parameters.AddWithValue("@Max_SEQ", tsRef.Max_SEQ);

                            cmd.Parameters.AddWithValue("@Min_LSN", tsRef.Min_LSN);
                            cmd.Parameters.AddWithValue("@Min_SEQ", tsRef.Min_SEQ);

                            //string Max_LSN = "0x" + BitConverter.ToString(tsRef.Max_LSN).Replace("-", "");
                            //string Min_LSN = "0x" + BitConverter.ToString(tsRef.Min_LSN).Replace("-", "");
                        }
                        #endregion

                        #region 执行查询
                        try
                        {
                            using (SqlConnection con = new SqlConnection(GetConnectionString(drRefDb)))
                            {
                                cmd.Connection = con;
                                cmd.CommandText = stbSelect.ToString();
                                SqlDataAdapter dad = new SqlDataAdapter(cmd);
                                dad.Fill(dtOutCdc);
                                dad.Dispose();
                                cmd.Dispose();
                                con.Close();
                            }
                        }
                        catch (Exception ex)
                        {
                            throw ex;
                        }
                        #endregion

                        if (dtOutCdc.Rows.Count == 0) continue;




                        //得到时刻分组//--按事务编号分事务批执行
                        List<DateTime> lstTime = GetTimeGroup(dtOutCdc);
                        //遍历事务分组
                        for (int iTime = 0; iTime < lstTime.Count; iTime++)
                        {
                            // 查出本次事务的捕获
                            DataRow[] drsOutCdc = dtOutCdc.Select(string.Format("__$ExecuteTime='{0}'", TimeToString(lstTime[iTime])), "__$ExecuteTime,__$start_lsn,__$seqval");//，__$operation

                            if (drsOutCdc.Length == 0) continue;

                            byte[] clrlsn = (byte[])drsOutCdc[0]["__$start_lsn"];
                            byte[] clrseq = null;
                            DateTime clrTime = lstTime[iTime];

                            #region 清理捕获
                            SqlConnection conn = null;
                            SqlTransaction tran = null;
                            int iCountInsert = 0;
                            int iCountUpdate = 0;
                            int iCountDelete = 0;
                            try
                            {
                                conn = new SqlConnection(GetConnectionString(drCurDb));
                                conn.Open();
                                tran = conn.BeginTransaction();//定义一个事务的开始
                                cmd.Connection = conn;
                                cmd.Transaction = tran;


                                #region 执行清理
                                for (int iOutCdc = 0; iOutCdc < drsOutCdc.Length; iOutCdc++)
                                {
                                    DataRow drOutCdc = drsOutCdc[iOutCdc];

                                    string operation = drOutCdc["__$operation"].ToString();

                                    switch (operation)
                                    {
                                        case "2":
                                            #region Insert_Delete
                                            StringBuilder stbInsert_Delete = new StringBuilder();
                                            stbInsert_Delete.Append(" DELETE FROM ");
                                            stbInsert_Delete.Append(NSCDC(strTableName));
                                            stbInsert_Delete.Append(" WHERE __$operation=2");
                                            for (int iColumn = 0; iColumn < dtColumn.Rows.Count; iColumn++)
                                            {
                                                string strColumn = dtColumn.Rows[iColumn]["ScName"].ToString();
                                                string strVarCol = string.Format("@{0}_Del", strColumn);

                                                object objColVal = drOutCdc[strColumn];
                                                if (objColVal != DBNull.Value)
                                                {
                                                    stbInsert_Delete.Append(" AND ");
                                                    stbInsert_Delete.Append(NSCDT(strColumn));
                                                    stbInsert_Delete.Append("=");
                                                    stbInsert_Delete.Append(strVarCol);
                                                    cmd.Parameters.AddWithValue(strVarCol, objColVal);
                                                }
                                                else
                                                {
                                                    stbInsert_Delete.Append(" AND ");
                                                    stbInsert_Delete.Append(NSCDT(strColumn));
                                                    stbInsert_Delete.Append(" IS NULL");
                                                }
                                            }
                                            //执行SQL
                                            cmd.CommandText = stbInsert_Delete.ToString();
                                            iCountInsert += cmd.ExecuteNonQuery(); cmd.Parameters.Clear();
                                            #endregion
                                            break;


                                        case "3": //3 = 更新（旧值）
                                            /*什么都不做了*/
                                            break;


                                        case "4"://4 = 更新（新值）
                                            #region Update_Delete
                                            //当 __$operation = 1 或 __$operation = 3 时,将始终为数据类型为 image text 和 ntext 的列指定一个 NULL 值
                                            //当 __$operation = 3 时,将为数据类型为 varbinary(max)、varchar(max) 或 nvarchar(max) 的列指定一个 NULL 值
                                            //除非该列在更新期间发生了更改,当 __$operation = 1 时,将为这些列指定删除时的值,捕获实例中包含的计算列的值始终为 NULL

                                            //拼接有所有列列表的删除语句
                                            StringBuilder stbUpdate_Delete = new StringBuilder();
                                            stbUpdate_Delete.Append(" DECLARE @lsn binary(10),@seq binary(10)");
                                            stbUpdate_Delete.Append(" SELECT @lsn=__$start_lsn,@seq=__$seqval");//,__$operation
                                            stbUpdate_Delete.Append(" FROM ");
                                            stbUpdate_Delete.Append(NSCDC(strTableName));
                                            stbUpdate_Delete.Append(" WHERE __$operation=4");
                                            for (int iColumn = 0; iColumn < dtColumn.Rows.Count; iColumn++)
                                            {
                                                //判断列的类型
                                                //string strCoType = dtColumn.Rows[iColumn]["ScType"].ToString().ToLower();
                                                //当 __$operation = 1 或 __$operation = 3 时,将始终为数据类型为 image text 和 ntext 的列指定一个 NULL 值
                                                //if (operation == "4"
                                                //    || (operation == "3"
                                                //    && strCoType != "image"
                                                //    && strCoType != "text"
                                                //    && strCoType != "ntext"
                                                //    && Convert.ToInt32(dtColumn.Rows[iColumn]["ScSize"]) != -1)//varbinary(max)、varchar(max) 或 nvarchar(max)                                                       
                                                //    )
                                                //{
                                                string strColumn = dtColumn.Rows[iColumn]["ScName"].ToString();
                                                string strVarCol = string.Format("@{0}_Del", strColumn);

                                                object objColVal = drOutCdc[strColumn];
                                                if (objColVal != DBNull.Value)
                                                {
                                                    stbUpdate_Delete.Append(" AND ");
                                                    stbUpdate_Delete.Append(NSCDT(strColumn));
                                                    stbUpdate_Delete.Append("=");
                                                    stbUpdate_Delete.Append(strVarCol);
                                                    cmd.Parameters.AddWithValue(strVarCol, objColVal);
                                                }
                                                else
                                                {
                                                    stbUpdate_Delete.Append(" AND (");
                                                    stbUpdate_Delete.Append(NSCDT(strColumn));
                                                    stbUpdate_Delete.Append(" IS NULL)");
                                                }
                                                //}
                                            }
                                            stbUpdate_Delete.Append(" \nDELETE FROM ");
                                            stbUpdate_Delete.Append(NSCDC(strTableName));
                                            stbUpdate_Delete.Append(" WHERE __$start_lsn=@lsn AND __$seqval=@seq");

                                            //执行SQL
                                            cmd.CommandText = stbUpdate_Delete.ToString();
                                            iCountUpdate += cmd.ExecuteNonQuery(); cmd.Parameters.Clear();
                                            #endregion
                                            break;


                                        case "1":
                                            #region Delete_Delete
                                            StringBuilder stbDelete_Delete = new StringBuilder();
                                            //拼接有所有列列表的删除语句
                                            stbDelete_Delete.Append(" DELETE FROM ");
                                            stbDelete_Delete.Append(NSCDC(strTableName));
                                            stbDelete_Delete.Append(" WHERE __$operation=1");
                                            for (int iColumn = 0; iColumn < dtColumn.Rows.Count; iColumn++)
                                            {
                                                //判断列的类型
                                                string strCoType = dtColumn.Rows[iColumn]["ScType"].ToString().ToLower();
                                                //当 __$operation = 1 或 __$operation = 3 时,将始终为数据类型为 image text 和 ntext 的列指定一个 NULL 值
                                                if (strCoType != "image" && strCoType != "text" && strCoType != "ntext")
                                                {
                                                    string strColumn = dtColumn.Rows[iColumn]["ScName"].ToString();
                                                    string strVarCol = string.Format("@{0}_Del", strColumn);
                                                    object objColVal = drOutCdc[strColumn];
                                                    if (objColVal != DBNull.Value)
                                                    {
                                                        stbDelete_Delete.Append(" AND ");
                                                        stbDelete_Delete.Append(NSCDT(strColumn));
                                                        stbDelete_Delete.Append("=");
                                                        stbDelete_Delete.Append(strVarCol);
                                                        cmd.Parameters.AddWithValue(strVarCol, objColVal);
                                                    }
                                                    else
                                                    {
                                                        stbDelete_Delete.Append(" AND ");
                                                        stbDelete_Delete.Append(NSCDT(strColumn));
                                                        stbDelete_Delete.Append(" IS NULL");
                                                    }
                                                }
                                            }
                                            cmd.CommandText = stbDelete_Delete.ToString();
                                            iCountDelete += cmd.ExecuteNonQuery(); cmd.Parameters.Clear();
                                            #endregion
                                            break;


                                        default: SM("数据意外:operation={0}", operation);
                                            break;
                                    }
                                    clrseq = (byte[])drsOutCdc[iOutCdc]["__$seqval"];
                                }
                                #endregion

                                #region 更新同步当前库中目标LSN
                                {
                                    syncline.ClrLsn(tsRef.SyncName, clrlsn, clrseq);
                                    //string strLsn = "0x" + BitConverter.ToString(clrlsn).Replace("-", "");//test
                                }
                                #endregion

                                tran.Commit();//提交事务
                                SMC(syncitem, iCurDb, iTable, iCountInsert, iCountUpdate, iCountDelete);//提示信息

                            }
                            catch (Exception ex)
                            {
                                //回滚事务
                                tran.Rollback();//这里没有根据事务日志号来进行
                                throw ex;
                            }
                            finally
                            {
                                try
                                {
                                    cmd.Dispose();
                                    tran.Dispose();
                                    conn.Close();
                                }
                                catch { }
                            }
                            #endregion
                        }
                    }
                }
            }
        }
        #endregion

        #region 获取捕获
        public static DataTable GetCdc(SyncItem syncitem, int iTable)//, int iSrcDb
        {
            DataTable dtCdc = new DataTable();

            DataTable dtSyncDb = syncitem.SyncDb;
            DataTable dtSyncTb = syncitem.SyncTb;

            string strTableName = dtSyncTb.Rows[iTable]["StName"].ToString();
            DataTable dtColumn = syncitem.SyncTb.Rows[iTable]["Column"] as DataTable;

            for (int iSrcDb = 0; iSrcDb < dtSyncDb.Rows.Count; iSrcDb++)
            {
                // 收集来源库有效的捕获
                DataRow drSrcDb = dtSyncDb.Rows[iSrcDb];
                try
                {

                    // 当前来源最小TimeSlot
                    TimeSlot tsCdcMin = new TimeSlot();

                    #region 得到不同目标库对应当前来源的最小日志操作记录

                    for (int iDesDb = 0; iDesDb < dtSyncDb.Rows.Count; iDesDb++)
                    {
                        if (iSrcDb == iDesDb) continue;//　不比较当前

                        TimeSlot tsCdcSrc = new SyncLine(GetSyncLineSfp(syncitem, iDesDb, iTable))[GetSyncName(syncitem, iSrcDb, iTable)];

                        if (tsCdcMin.Max_LSN == TimeSlot.Empty)
                        {
                            tsCdcMin = tsCdcSrc;
                        }
                        else if (ByteArrayCompare(tsCdcSrc.Max_LSN, tsCdcMin.Max_LSN) < 0)
                        {
                            tsCdcMin = tsCdcSrc;
                        }
                        else if (ByteArrayCompare(tsCdcSrc.Max_LSN, tsCdcMin.Max_LSN) == 0)
                        {
                            if (ByteArrayCompare(tsCdcSrc.Max_SEQ, tsCdcMin.Max_SEQ) < 0)
                            {
                                tsCdcMin = tsCdcSrc;
                            }
                        }
                    }
                    #endregion

                    //if (tsCdcMin.Max_LSN == TimeSlot.Empty) continue;//为零时，不同步

                    #region 拼接捕获查询SQL
                    SqlCommand cmd = new SqlCommand();
                    StringBuilder stbSelect = new StringBuilder();
                    {
                        stbSelect.Append(" SELECT TOP "); stbSelect.Append(iTableMaxCdcCount);
                        stbSelect.Append(" __$ExecuteTime=sys.fn_cdc_map_lsn_to_time(__$start_lsn)");
                        stbSelect.Append(",__$SyncDbIndex="); stbSelect.Append(iSrcDb);
                        stbSelect.Append(",*");
                        stbSelect.Append(" FROM "); stbSelect.Append(NSCDC(strTableName));
                        stbSelect.Append(" WHERE (__$start_lsn=@Max_LSN AND __$seqval>@Max_SEQ)");
                        stbSelect.Append(" OR (__$start_lsn>@Max_LSN)");
                        stbSelect.Append(" ORDER BY __$start_lsn,__$seqval,__$operation");

                        cmd.Parameters.AddWithValue("@Max_LSN", tsCdcMin.Max_LSN);
                        cmd.Parameters.AddWithValue("@Max_SEQ", tsCdcMin.Max_SEQ);

                        //string tsSrcDb_LSN = "0x" + BitConverter.ToString(tsCdcMin.Max_LSN).Replace("-", "");
                        //string tsSrcDb_SEQ = "0x" + BitConverter.ToString(tsCdcMin.Max_SEQ).Replace("-", "");
                    }
                    #endregion

                    #region 执行查询
                    using (SqlConnection con = new SqlConnection(GetConnectionString(drSrcDb)))
                    {
                        cmd.Connection = con;
                        cmd.CommandText = stbSelect.ToString();
                        SqlDataAdapter dad = new SqlDataAdapter(cmd);
                        dad.Fill(dtCdc);
                        dad.Dispose();
                        cmd.Dispose();
                        con.Close();
                    }
                    #endregion

                    #region 去除被拆开的一半的更新语句
                    if (dtCdc.Rows.Count >= iTableMaxCdcCount)
                    {
                        int iLastIndex = dtCdc.Rows.Count - 1;
                        if (3 == Convert.ToInt32(dtCdc.Rows[iLastIndex]["__$operation"]))
                        {
                            dtCdc.Rows.RemoveAt(iLastIndex);
                        }
                    }
                    #endregion


                }
                catch (Exception eSelect)
                {
                    throw eSelect;
                }
            }
            return dtCdc;
        }
        #endregion

        #region 同步数据
        public static void SyncData(SyncItem syncitem)
        {
            DataTable dtSyncTb = syncitem.SyncTb;
            DataTable dtSyncDb = syncitem.SyncDb;
            DataTable dtSyncdc = null;//捕获记录
            try
            {

                for (int iTable = 0; iTable < dtSyncTb.Rows.Count; iTable++)
                {
                    DataRow drSyncTb = dtSyncTb.Rows[iTable];//当前对象
                    DataTable dtColumn = syncitem.SyncTb.Rows[iTable]["Column"] as DataTable;// 当前表的列
                    List<string> lstColumn = GetUniqueColumn(dtColumn);
                    if (lstColumn.Count == 0)
                    {
                        SM("无法同步:[{0}],[{1}],该表没有主键,暂时无法同步,请为其指定唯一列!", syncitem.SgName, drSyncTb["StName"]);
                        continue;
                    }

                    #region 获取当前表的需要同步的捕获
                    try
                    {
                        dtSyncdc = GetCdc(syncitem, iTable);//, iSrcDb
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    #endregion

                    // 没有一条捕获需要[同步]时,下一表
                    if (dtSyncdc.Rows.Count == 0) continue;
                   

                    #region 遍历同步目标数据库

                    for (int iDesDb = 0; iDesDb < dtSyncDb.Rows.Count; iDesDb++)
                    {
                        DataRow drDesDb = dtSyncDb.Rows[iDesDb];//当前对象

                        SyncLine desSyncLine = new SyncLine(GetSyncLineSfp(syncitem, iDesDb, iTable));

                        //得到时刻分组//--按事务编号分事务批执行
                        List<DateTime> lstTime = GetTimeGroup(dtSyncdc);
                        //遍历事务分组
                        for (int iTime = 0; iTime < lstTime.Count; iTime++)
                        {
                            DateTime newTime = lstTime[iTime];//当前对象

                            // 遍历捕获来源数据库
                            for (int iSrcDb = 0; iSrcDb < dtSyncDb.Rows.Count; iSrcDb++)
                            {
                                if (iSrcDb == iDesDb) continue;

                                DataRow drSrcDb = dtSyncDb.Rows[iSrcDb];//当前对象

                                TimeSlot tsSrc = desSyncLine[GetSyncName(syncitem, iSrcDb, iTable)];

                                // 查出本次事务的捕获
                                string strWhere = string.Format("__$ExecuteTime='{0}' AND __$SyncDbIndex={1} AND __$LsnSequence>'{2}'", TimeToString(lstTime[iTime]), iSrcDb, TimeSlot.GetLsnSequence(tsSrc.Max_LSN,tsSrc.Max_SEQ));
                                string strOrder = "__$ExecuteTime,__$start_lsn,__$seqval,__$operation";

                                DataRow[] drsSyncdc = dtSyncdc.Select(strWhere, strOrder);

                                if (drsSyncdc.Length == 0)continue;
                                //{
                                //    SM("是同步的:[{0}],[{1}].[{2}].dbo.[{3}]"
                                //        , syncitem.SgName
                                //        , drDesDb["Server"]
                                //        , drDesDb["Database"]
                                //        , drSyncTb["StName"]);
                                //}

                                byte[] synlsn = (byte[])drsSyncdc[0]["__$start_lsn"];
                                byte[] synseq = null;

                                int iCountInsert = 0;
                                int iCountUpdate = 0;
                                int iCountDelete = 0;
                                SqlConnection conn = null;
                                SqlTransaction tran = null;
                                SqlCommand cmd = new SqlCommand();
                                try
                                {
                                    conn = new SqlConnection(GetConnectionString(drDesDb));
                                    conn.Open();
                                    //定义一个事务的开始
                                    tran = conn.BeginTransaction();
                                    cmd.Connection = conn;
                                    cmd.Transaction = tran;

                                    #region 遍历该时刻事务内的捕获记录,按操作类型,执行同步
                                    for (int iSyncdc = 0; iSyncdc < drsSyncdc.Length; iSyncdc++)
                                    {
                                        DataRow drSyncdc = drsSyncdc[iSyncdc];//当前对象
                                        string operation = Convert.ToString(drSyncdc["__$operation"]);
                                        switch (operation)
                                        {
                                            case "2":
                                                #region Insert
                                                if (iSrcDb == iDesDb) continue;//不用同步自己   
                                                try
                                                {
                                                    //执行SQL
                                                    cmd.CommandText = GetSyncInsertSql(drSyncdc, drSyncTb["StName"].ToString(), dtColumn, cmd);
                                                    iCountInsert += cmd.ExecuteNonQuery(); cmd.Parameters.Clear();
                                                    //SMS(syncitem, iDesDb, iTable, iCountInsert, iCountUpdate, iCountDelete);
                                                }
                                                catch (Exception ex)
                                                {
                                                    throw ex;
                                                }
                                                #endregion
                                                break;


                                            case "3"://3 = 更新（旧值） //case "4"://4 = 更新（新值）
                                                //if (iSrcDb == iDesDb)continue;//ggz?!?不用同步自己   
                                                #region Update
                                                try
                                                {
                                                    ////条件判断(不然就是只过滤了4而没过滤3)
                                                    //if (iSyncdc + 1 < drsSyncdc.Length
                                                    //    && Convert.ToBase64String((byte[])drSyncdc["__$start_lsn"]) == Convert.ToBase64String((byte[])drsSyncdc[iSyncdc + 1]["__$start_lsn"])
                                                    //    && Convert.ToInt32(drsSyncdc[iSyncdc + 1]["__$operation"]) == 4
                                                    //    )
                                                    //{
                                                    //执行SQL
                                                    cmd.CommandText = GetSyncUpdateSql(drSyncdc, drsSyncdc[++iSyncdc], drSyncTb["StName"].ToString(), dtColumn, cmd);
                                                    iCountUpdate += cmd.ExecuteNonQuery(); cmd.Parameters.Clear();
                                                    //SMS(syncitem, iDesDb, iTable, iCountInsert, iCountUpdate, iCountDelete);
                                                    //}
                                                    //else
                                                    //{
                                                    //    continue;//更新要更新自己
                                                    //}
                                                }
                                                catch (Exception ex)
                                                {
                                                    throw ex;
                                                }
                                                #endregion
                                                break;


                                            case "1":
                                                #region Deletel
                                                if (iSrcDb == iDesDb) continue;//不用同步自己   
                                                try
                                                {
                                                    //执行SQL
                                                    cmd.CommandText = GetSyncDeleteSql(drSyncdc, drSyncTb["StName"].ToString(), dtColumn, cmd);
                                                    iCountDelete += cmd.ExecuteNonQuery(); cmd.Parameters.Clear();
                                                    //SMS(syncitem, iDesDb, iTable, iCountInsert, iCountUpdate, iCountDelete);
                                                }
                                                catch (Exception ex)
                                                {
                                                    throw ex;
                                                }
                                                #endregion
                                                break;


                                            default: SMS(syncitem, iDesDb, iTable, 0, 0, 0);
                                                break;
                                        }
                                        synseq = (byte[])drsSyncdc[iSyncdc]["__$seqval"];
                                    }

                                    #endregion

                                    #region 更新同步当前库中目标LSN
                                    {
                                        desSyncLine.SynLsn(GetSyncName(syncitem, iSrcDb, iTable), synlsn, synseq);
                                        //string strLsn = "0x" + BitConverter.ToString(synlsn).Replace("-", "");//test
                                    }
                                    #endregion

                                    #region 完成事务
                                    //提交事务
                                    tran.Commit();
                                    // 提示信息
                                    SMS(syncitem, iDesDb, iTable, iCountInsert, iCountUpdate, iCountDelete);
                                    #endregion
                                }
                                catch (Exception ex)
                                {
                                    //回滚事务
                                    tran.Rollback();
                                    throw ex;
                                }
                                finally
                                {
                                    try
                                    {
                                        cmd.Dispose();
                                        conn.Close();
                                    }
                                    catch { }
                                }
                            }
                        }
                    }
                    #endregion
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #endregion

        #region 同步 SQL

        public static string GetSyncInsertSql(DataRow drSyncdc, string strTableName, DataTable dtColumn, SqlCommand cmd)
        {
            #region 直接插入语句
            StringBuilder stbInsert = new StringBuilder();
            {
                StringBuilder stbInsertLeft = new StringBuilder();
                StringBuilder stbInsertRigh = new StringBuilder();
                stbInsertLeft.Append(" INSERT INTO dbo.[");
                stbInsertLeft.Append(strTableName);
                stbInsertLeft.Append("] (");
                stbInsertLeft.Append("");
                stbInsertLeft.Append("");
                stbInsertLeft.Append("");

                stbInsertRigh.Append(")VALUES(");
                bool hasComma = false;
                for (int iColumn = 0; iColumn < dtColumn.Rows.Count; iColumn++)
                {
                    string strColumn = dtColumn.Rows[iColumn]["ScName"].ToString();
                    string strVarCol = "@" + strColumn + "_New";
                    object objVal = drSyncdc[strColumn];
                    if (objVal != DBNull.Value)//不为空时才设置
                    {
                        if (hasComma)
                        {
                            stbInsertLeft.Append(",");
                            stbInsertRigh.Append(",");
                        }

                        stbInsertLeft.Append(NSCDT(strColumn));
                        stbInsertRigh.Append(strVarCol);

                        cmd.Parameters.AddWithValue(strVarCol, objVal);

                        hasComma = true;
                    }
                }
                stbInsertRigh.Append(")");

                stbInsert.Append(stbInsertLeft);
                stbInsert.Append(stbInsertRigh);
            }
            #endregion

            string strIdentity = GetIdentity(dtColumn);

            if (string.IsNullOrEmpty(strIdentity))
            {
                return stbInsert.ToString();
            }
            else
            {
                #region 识列插入语句
                StringBuilder stbInsertIdentity = new StringBuilder();
                {
                    stbInsertIdentity.Append(" SET IDENTITY_INSERT dbo.[");
                    stbInsertIdentity.Append(strTableName);
                    stbInsertIdentity.Append("] ON");
                    stbInsertIdentity.Append(stbInsert);//接入插入语句
                    stbInsertIdentity.Append(" SET IDENTITY_INSERT dbo.[");
                    stbInsertIdentity.Append(strTableName);
                    stbInsertIdentity.Append("] OFF");
                }
                #endregion

                return stbInsertIdentity.ToString();
            }
        }

        public static string GetSyncUpdateSql(DataRow drOld, DataRow drNew, string strTableName, DataTable dtColumn, SqlCommand cmd)
        {

            #region 赋值 语句 stbUpdateValue
            StringBuilder stbUpdateValue = new StringBuilder();
            stbUpdateValue.Append(" UPDATE [");
            stbUpdateValue.Append(strTableName);
            stbUpdateValue.Append("] SET ");//如果只有一列是标识列,还真没法同步,哈哈!

            bool hasComma = false;//用来控制","号
            string strIdentity = GetIdentity(dtColumn); //标识列//ggz!这个也要放入配置.:已经放入了
            for (int iColumn = 0; iColumn < dtColumn.Rows.Count; iColumn++)// 遍历列进行比较,以后改成可配标示列的(先默认主键,再唯一约束列,再自增长<自增长其实并不可靠>)
            {//ggz:operation 就不判断了
                string strColumn = dtColumn.Rows[iColumn]["ScName"].ToString();

                if (strIdentity != strColumn) //标识列,不用做更新,仅做更新条件
                {
                    string strVarNew = "@" + strColumn + "_New";

                    // 只更新需要更新的列
                    bool needSync = false;
                    if ((drOld[strColumn] != DBNull.Value) && (drNew[strColumn] != DBNull.Value))
                    {
                        if (drNew[strColumn].GetType() == typeof(System.Byte[]))
                        {
                            if (Convert.ToBase64String((byte[])drOld[strColumn]) != Convert.ToBase64String((byte[])drNew[strColumn]))
                            {
                                needSync = true;
                            }
                        }
                        // ggz?可能还存在问题，这里的判断可以再深入研究一下，如果没有的话，那就建个类，专门处理这种比较
                        else
                        {
                            if (drNew[strColumn].ToString() != drOld[strColumn].ToString())
                            {
                                needSync = true;
                            }
                        }
                    }
                    else if (drOld[strColumn] != DBNull.Value || drNew[strColumn] != DBNull.Value)
                    {
                        needSync = true;
                    }

                    if (needSync)
                    {
                        if (hasComma)
                        {
                            stbUpdateValue.Append(",");
                        }
                        stbUpdateValue.Append("[");
                        stbUpdateValue.Append(strColumn);
                        stbUpdateValue.Append("]=");
                        stbUpdateValue.Append(strVarNew);

                        cmd.Parameters.AddWithValue(strVarNew, drNew[strColumn]);

                        hasComma = true;
                    }
                }
            }
            #endregion

            #region 通列更新条件 stbUpdateColumnWhere
            StringBuilder stbUpdateColumnWhere = new StringBuilder();
            stbUpdateColumnWhere.Append(" WHERE 1=1");
            // 遍历列进行比较,以后改成可配标示列的(先默认主键,再唯一约束列,再自增长<自增长其实并不可靠>)
            for (int iColumn = 0; iColumn < dtColumn.Rows.Count; iColumn++)//operation 就不判断了
            {
                string strColumn = dtColumn.Rows[iColumn]["ScName"].ToString();

                string strCoType = dtColumn.Rows[iColumn]["ScType"].ToString().ToLower();
                //当 __$operation = 1 或 __$operation = 3 时,将始终为数据类型为 image text 和 ntext 的列指定一个 NULL 值
                if (strCoType != "image" && strCoType != "text" && strCoType != "ntext")
                {
                    //当 __$operation = 3 时,将为数据类型为 varbinary(max)、varchar(max) 或 nvarchar(max) 的列指定一个 NULL 值(ggz:xml)
                    if (Convert.ToInt32(dtColumn.Rows[iColumn]["ScSize"]) != -1)
                    {
                        string strVarOld = "@" + strColumn + "Old";
                        object objColVal = drOld[strColumn];
                        if (objColVal != DBNull.Value)
                        {
                            stbUpdateColumnWhere.Append(" AND ");
                            stbUpdateColumnWhere.Append(NSCDT(strColumn));
                            stbUpdateColumnWhere.Append("=");
                            stbUpdateColumnWhere.Append(strVarOld);
                            cmd.Parameters.AddWithValue(strVarOld, objColVal);
                        }
                        else
                        {
                            stbUpdateColumnWhere.Append(" AND ");
                            stbUpdateColumnWhere.Append(NSCDT(strColumn));
                            stbUpdateColumnWhere.Append(" IS NULL");
                        }
                    }
                }
            }
            #endregion

            #region 唯一更新条件 stbUpdateUnqiueWhere
            StringBuilder stbUpdateUnqiueWhere = new StringBuilder();
            stbUpdateUnqiueWhere.Append(" WHERE 1=1");
            List<string> lstUniCol = GetUniqueColumn(dtColumn);//唯一列
            for (int iUniCol = 0; iUniCol < lstUniCol.Count; iUniCol++)
            {
                string strColumn = lstUniCol[iUniCol];
                string strVarOld = "@" + strColumn + "_Old";
                object objColVal = drOld[strColumn];

                if (objColVal != DBNull.Value)
                {
                    stbUpdateUnqiueWhere.Append(" AND ");
                    stbUpdateUnqiueWhere.Append(NSCDT(strColumn));
                    stbUpdateUnqiueWhere.Append("=");
                    stbUpdateUnqiueWhere.Append(strVarOld);
                    cmd.Parameters.AddWithValue(strVarOld, objColVal);
                }
                else
                {
                    stbUpdateUnqiueWhere.Append(" AND ");
                    stbUpdateUnqiueWhere.Append(NSCDT(strColumn));
                    stbUpdateUnqiueWhere.Append(" IS NULL");
                }
            }
            #endregion

            #region 智能更新语句　stbUpdateSmart
            StringBuilder stbUpdateSmart = new StringBuilder();
            stbUpdateSmart.Append(" IF EXISTS(");
            stbUpdateSmart.Append(" SELECT 1 FROM ");
            stbUpdateSmart.Append(NSCDT(strTableName));
            stbUpdateSmart.Append(stbUpdateColumnWhere);
            stbUpdateSmart.Append(" )");
            stbUpdateSmart.Append(" BEGIN ");
            {
                stbUpdateSmart.Append(stbUpdateValue);
                stbUpdateSmart.Append(stbUpdateColumnWhere);
            }
            stbUpdateSmart.Append(" END");
            stbUpdateSmart.Append(" ELSE");
            stbUpdateSmart.Append(" BEGIN");
            {
                stbUpdateSmart.Append(stbUpdateValue);
                stbUpdateSmart.Append(stbUpdateUnqiueWhere);
                //根据IsMain判断＝时的情况AND @__$ExecuteTime>=更新
                stbUpdateSmart.Append(" AND @__$ExecuteTime>=("); cmd.Parameters.AddWithValue("@__$ExecuteTime", drOld["__$ExecuteTime"]);
                {
                    stbUpdateSmart.Append(" SELECT TOP 1 sys.fn_cdc_map_lsn_to_time(__$start_lsn)");
                    stbUpdateSmart.Append(" FROM ");
                    stbUpdateSmart.Append(NSCDC(strTableName));
                    stbUpdateSmart.Append(" AS CT");
                    stbUpdateSmart.Append(" WHERE  __$operation=4");
                    stbUpdateSmart.Append(" AND EXISTS(");
                    {
                        stbUpdateSmart.Append(" SELECT 1");
                        stbUpdateSmart.Append(" FROM ");
                        stbUpdateSmart.Append(NSCDT(strTableName));
                        stbUpdateSmart.Append(" AS DT");
                        stbUpdateSmart.Append(stbUpdateUnqiueWhere);
                        for (int iColumn = 0; iColumn < dtColumn.Rows.Count; iColumn++)
                        {
                            string strNColumn = NSCDT(dtColumn.Rows[iColumn]["ScName"]);
                            stbUpdateSmart.Append(" AND ISNULL(DT.");
                            stbUpdateSmart.Append(strNColumn);
                            stbUpdateSmart.Append(" ,1)=ISNULL(DT.");
                            stbUpdateSmart.Append(strNColumn);
                            stbUpdateSmart.Append(" ,1)");
                        }
                    }
                    stbUpdateSmart.Append(" )");
                    stbUpdateSmart.Append(" ORDER BY __$start_lsn DESC");
                }
                stbUpdateSmart.Append(" )");
            }
            stbUpdateSmart.Append(" END");


            #endregion

            return stbUpdateSmart.ToString();
        }

        public static string GetSyncDeleteSql(DataRow drSyncdc, string strTableName, DataTable dtColumn, SqlCommand cmd)
        {
            StringBuilder stbDelete = new StringBuilder();
            //拼接有所有列列表的删除语句
            stbDelete.Append(" DELETE FROM [");
            stbDelete.Append(strTableName);
            stbDelete.Append("] WHERE 1=1");

            List<string> lstUniCol = GetUniqueColumn(dtColumn);//唯一列

            for (int iUniCol = 0; iUniCol < lstUniCol.Count; iUniCol++)
            {
                string strColumn = lstUniCol[iUniCol];
                string strVarCol = "@" + strColumn + "_Del";
                object objColVal = drSyncdc[strColumn];
                if (objColVal != DBNull.Value)
                {
                    stbDelete.Append(" AND ");
                    stbDelete.Append(NSCDT(strColumn));
                    stbDelete.Append("=");
                    stbDelete.Append(strVarCol);
                    cmd.Parameters.AddWithValue(strVarCol, objColVal);
                }
                else
                {
                    stbDelete.Append(" AND ");
                    stbDelete.Append(NSCDT(strColumn));
                    stbDelete.Append(" IS NULL");
                }
            }
            return stbDelete.ToString();
        }

        #endregion

        #region 基础转换
        //private static List<DateTime>GetValTime(DataTable dtSyncdc,int iDesDb)
        //{
        //     List<DateTime> lstValTime = new List<DateTime>();

        //     return lstValTime;
        //}
        private static List<DateTime> GetTimeGroup(DataTable dtSyncdc)
        {
            List<DateTime> lstTimeGroup = new List<DateTime>();

            DateTime timeComp = DateTime.Now;
            DateTime timeSlot = DateTime.Now;

            if (dtSyncdc.Columns.IndexOf("__$LsnSequence") < 0)
            {
                dtSyncdc.Columns.Add("__$LsnSequence");
            }

            //stbSelect.Append(",__$LsnSequence=(SUBSTRING (__$start_lsn, 1, 10)+SUBSTRING (__$seqval, 1, 10))");
            DataRow[] drsSyncdc = dtSyncdc.Select("1=1", "__$ExecuteTime,__$start_lsn,__$seqval");
            for (int iSyncdc = 0; iSyncdc < drsSyncdc.Length; iSyncdc++)
            {
                drsSyncdc[iSyncdc]["__$LsnSequence"] = TimeSlot.GetLsnSequence((byte[])drsSyncdc[iSyncdc]["__$start_lsn"], (byte[])drsSyncdc[iSyncdc]["__$seqval"]);
                timeSlot = Convert.ToDateTime(drsSyncdc[iSyncdc]["__$ExecuteTime"]);

                if (timeComp != timeSlot)
                {
                    lstTimeGroup.Add(timeSlot);
                    timeComp = timeSlot;
                }
            }
            return lstTimeGroup;
        }

        // Time to String
        public static string TimeToString(DateTime datetime)
        {
            return datetime.ToString("yyyy-MM-dd HH:mm:ss.fff");
        }

        // 比较两个字节数组的大小
        public static int ByteArrayCompare(byte[] ba1, byte[] ba2)
        {
            int iLength1 = ba1.Length;
            int iLength2 = ba2.Length;

            if (iLength1 > iLength2)
            {
                return 1;
            }
            else if (iLength1 < iLength2)
            {
                return -1;
            }
            else
            {
                for (int i = 0; i < iLength1; i++)
                {
                    if (ba1[i] > ba2[i])
                    {
                        return 1;
                    }
                    else if (ba1[i] < ba2[i])
                    {
                        return -1;
                    }
                }
                return 0;
            }
        }

        #endregion

        #region 文件路径
        // 文件路径
        public static readonly string strSyncLineDirPath = System.Windows.Forms.Application.StartupPath + "\\lsn";

        // 路径检测
        static SqlHelper()
        {
            //检测目录是否存在,不存在则创建\timeslot
            DirectoryInfo diSyncLineDirPath = new DirectoryInfo(strSyncLineDirPath);
            if (!diSyncLineDirPath.Exists)
            {
                diSyncLineDirPath.Create();
            }
        }

        // SyncLine
        public static string GetSyncLineSfp(SyncItem syncitem, int iDbase, int iTable)
        {
            return string.Format("{0}\\{1}.xml", strSyncLineDirPath, GetSyncName(syncitem, iDbase, iTable));
        }
        // SyncName
        public static string GetSyncName(SyncItem syncitem, int iDbase, int iTable)
        {
            return string.Format("[{0}],[{1}].[{2}].dbo.[{3}]"
                        , PSC(syncitem.SgName)
                        , PSC(syncitem.SyncDb.Rows[iDbase]["Server"])
                        , syncitem.SyncDb.Rows[iDbase]["Database"]
                        , syncitem.SyncTb.Rows[iTable]["StName"]);
        }
        public static string GetGsd(string strGroup, string strServer, string strDatabase)
        {
            return string.Format("[{0}],[{1}].[{2}]"
                        , PSC(strGroup)
                        , PSC(strServer)
                        , strDatabase);
        }

        // 路径安全性转换
        private static string PSC(object strName)
        {
            return strName.ToString().Replace("\\", "@");//localhost\mssql2008r2
        }

        private static string NSCDT(object str)
        {
            return string.Format("[{0}]", str);
        }
        private static string NSCDC(object str)
        {
            return string.Format("cdc.dbo_{0}_CT", str);
        }
        #endregion

        #region 数据库基础操作
        // 返回指定时间对应的日志号
        public static byte[] GetMapTimeToLsn(object tracking_time, string strCon)
        {
            try
            {
                using (SqlConnection con = new SqlConnection(strCon))
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.CommandText = "SELECT sys.fn_cdc_map_time_to_lsn('largest less than or equal',@tracking_time)";
                    cmd.Connection = con;
                    cmd.Parameters.AddWithValue("@tracking_time", tracking_time);
                    con.Open();
                    object objLsn = cmd.ExecuteScalar();
                    if (objLsn != null && objLsn != DBNull.Value)
                    {
                        return (byte[])objLsn;
                    }
                    return null;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }




        // 测试数据库连接,检测数据库版本
        public static string TestConnection(string strServer, string strUserID, string strPassword)
        {
            //获取数据库连接字符串
            string strCon = GetConnectionString(strServer, strUserID, strPassword);
            string strSql = "SELECT 1 WHERE CONVERT(FLOAT,LEFT(CONVERT(VARCHAR(50),SERVERPROPERTY('ProductVersion')),2))>=10";
            //创建连接对象
            SqlConnection sqlCon = new SqlConnection(strCon);
            try
            {
                SqlCommand sqlCmd = new SqlCommand();
                sqlCmd.CommandText = strSql;
                sqlCmd.Connection = sqlCon;
                //打开连接
                sqlCon.Open();
                object objResult = sqlCmd.ExecuteScalar(); sqlCmd.Dispose();
                if (objResult == null)
                {
                    return "连接成功,但是数据库版本过低,暂时无法配置同步!";
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {//打开不成功 则连接不成功
                return ex.Message;
            }
            finally
            {
                if (sqlCon != null)
                {
                    sqlCon.Close();
                }
            }
        }

        // 获取当前服务器中的所有数据库(有权限的！！！！)
        public static DataTable GetDatabases(string strServer, string strUserID, string strPassword)
        {
            DataTable dt = null;

            //获取数据库连接字符串
            string strCon = GetConnectionString(strServer, strUserID, strPassword);

            string strSql = "select name from sys.databases where name not in('master','tempdb','model','msdb','distribution')";/*,database_id*/

            SqlDataAdapter dad = null;
            try
            {
                dad = new SqlDataAdapter(strSql, strCon);
                dt = new DataTable();
                dad.Fill(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dad != null)
                {
                    dad.Dispose();
                }
            }
            return dt;
        }


        #region 获取数据表的列

        // 返回指定同步库中表的所有列
        private static DataTable GetColumns(SyncItem syncitem, int iDbase, int iTable)
        {
            try
            {
                return GetColumns(syncitem.SyncDb.Rows[iDbase], syncitem.SyncTb.Rows[iTable]["StName"].ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        // 返回指定同步库中表的所有列
        private static DataTable GetColumns(DataRow drSyncDb, string strTable)
        {
            try
            {
                return GetColumns(GetConnectionString(drSyncDb), strTable);
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        // 返回指定同步库中表的所有列
        public static DataTable GetColumns(string strConnection, string strTable)
        {
            StringBuilder stbSql = new StringBuilder();
            {
                stbSql.Append(" SELECT sc.name as scname,st.name as sctype,sc.max_length as scsize");
                stbSql.Append(" ,ScPkey=(CASE WHEN ik.COLUMN_NAME IS NULL THEN 0 ELSE 1 END)");
                stbSql.Append(" ,ScIdtt=sc.is_identity");
                stbSql.Append(" ,DeOnly=0");
                stbSql.Append(" FROM sys.columns sc");
                stbSql.Append(" LEFT JOIN sys.types st");
                stbSql.Append(" ON st.user_type_id=sc.user_type_id ");
                stbSql.Append(" LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ik");
                stbSql.Append(" ON ik.COLUMN_NAME=sc.name AND object_id(ik.TABLE_NAME)=sc.object_id");
                stbSql.Append(" WHERE object_id=object_id('");
                stbSql.Append(strTable);
                stbSql.Append("') order by column_id");
            }

            DataTable dtColumn = SyncItem.CreateColumnTable();
            SqlDataAdapter dad = null;
            try
            {
                dad = new SqlDataAdapter(stbSql.ToString(), strConnection);
                dad.Fill(dtColumn);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                dad.Dispose();
            }
            return dtColumn;
        }

        // 返回列列表","号分隔的
        private static string GetColumnString(DataTable dtColumn)
        {
            StringBuilder stbColumnNameList = new StringBuilder();
            for (int i = 0; i < dtColumn.Rows.Count; i++)
            {
                stbColumnNameList.Append(",[");
                stbColumnNameList.Append(dtColumn.Rows[i]["ScName"]);
                stbColumnNameList.Append("]");
            }
            return stbColumnNameList.ToString().Substring(1);
        }

        #endregion

        // 返回唯一列
        public static List<string> GetUniqueColumn(DataTable dtColumn)
        {
            List<string> lstUniqueColumn = new List<string>();

            //查找主键约束唯一
            for (int iColumn = 0; iColumn < dtColumn.Rows.Count; iColumn++)
            {
                if (Convert.ToBoolean(dtColumn.Rows[iColumn]["ScPkey"]))
                {
                    lstUniqueColumn.Add(dtColumn.Rows[iColumn]["ScName"].ToString());
                }
            }
            //如果不存在主键则
            if (lstUniqueColumn.Count == 0)
            {
                //查找用户定义唯一
                for (int iColumn = 0; iColumn < dtColumn.Rows.Count; iColumn++)
                {
                    if (Convert.ToBoolean(dtColumn.Rows[iColumn]["DeOnly"]))
                    {
                        lstUniqueColumn.Add(dtColumn.Rows[iColumn]["ScName"].ToString());
                    }
                }
            }
            return lstUniqueColumn;
        }
        // 主键唯一
        public static List<string> GetScPkeyUniqueColumn(DataTable dtColumn)
        {
            List<string> lstScPkeyUniqueColumn = new List<string>();
            //查找主键唯一
            for (int iColumn = 0; iColumn < dtColumn.Rows.Count; iColumn++)
            {
                if (Convert.ToBoolean(dtColumn.Rows[iColumn]["ScPkey"]))
                {
                    lstScPkeyUniqueColumn.Add(dtColumn.Rows[iColumn]["ScName"].ToString());
                }
            }
            return lstScPkeyUniqueColumn;
        }
        // 户定义唯一
        public static List<string> GetDeOnlyUniqueColumn(DataTable dtColumn)
        {
            List<string> lstDealareUniqueColumn = new List<string>();
            //查找用户定义唯一
            for (int iColumn = 0; iColumn < dtColumn.Rows.Count; iColumn++)
            {
                if (Convert.ToBoolean(dtColumn.Rows[iColumn]["DeOnly"]))
                {
                    lstDealareUniqueColumn.Add(dtColumn.Rows[iColumn]["ScName"].ToString());
                }
            }
            return lstDealareUniqueColumn;
        }

        // 返回标示列名,无标识列时,返回null
        public static string GetIdentity(DataTable dtColumn)
        {
            for (int iColumn = 0; iColumn < dtColumn.Rows.Count; iColumn++)
            {
                if (Convert.ToBoolean(dtColumn.Rows[iColumn]["ScIdtt"]))
                {
                    return dtColumn.Rows[iColumn]["ScName"].ToString();
                }
            }
            return null;
        }

        #region ggz:返回当前服务器数据库中的所有表[主外键顺序的]<(有权限的难呐)>
        public static void FillSyncDbSyncTb(SyncItem syncitem)
        {
            string strServer = string.Empty;
            string strDatabase = string.Empty;
            string strUserID = string.Empty;
            string strPassword = string.Empty;

            if (syncitem.SyncDb.Rows.Count > 0)
            {//以主数据库中存在的表为准
                syncitem.SyncDb.Rows[0]["IsMain"] = true;
                strServer = syncitem.SyncDb.Rows[0]["Server"].ToString();
                strDatabase = syncitem.SyncDb.Rows[0]["Database"].ToString();

                //根据服务器名获取数据连接的用户名和密码
                XmlHelper.ConfigureConnectionInfo(strServer, ref strUserID, ref strPassword);
            }
            else
            {
                syncitem.SyncTb.Clear();
                return;
            }

            StringBuilder stbSql = new StringBuilder();
            {
                stbSql.Append(" SELECT DISTINCT sox.name as xoname,sor.name as roname");
                stbSql.Append(" FROM sys.tables sox");
                stbSql.Append(" LEFT JOIN  sysforeignkeys ON sysforeignkeys.fkeyid = sox.object_id AND sysforeignkeys.fkeyid <> sysforeignkeys.rkeyid");
                stbSql.Append(" LEFT JOIN  sys.tables sor ON sysforeignkeys.rkeyid = sor.object_id");
                stbSql.Append(" WHERE sox.is_ms_shipped=0");
                stbSql.Append(" ORDER BY sor.name,sox.name");
            }
            //获取数据库连接字符串
            string strCon = GetConnectionString(strServer, strUserID, strPassword, strDatabase);
            DataTable dt = new DataTable();
            SqlDataAdapter dad = null;
            try
            {
                dad = new SqlDataAdapter(stbSql.ToString(), strCon);
                dad.Fill(dt);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dad != null)
                {
                    dad.Dispose();
                }
            }


            #region 排序
            List<string> lstTables = new List<string>();

            //先添加不依赖别表的表
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string strRname = dt.Rows[i]["roname"].ToString();
                if (strRname == "")
                {
                    lstTables.Add(dt.Rows[i]["xoname"].ToString());
                    dt.Rows.RemoveAt(i);
                    i--;
                }
            }
            //递归计数器
            int iBlindRecursiveCount = 0;
            try
            {
                BlindSortTable(ref dt, ref lstTables, ref iBlindRecursiveCount);
            }
            catch (Exception ex)
            {
                throw ex;
                //或者查处所有表，不排序，并提示信息
            }
            #endregion

            #region 赋值到同步配置对象
            syncitem.SyncTb.Rows.Clear();
            for (int i = 0; i < lstTables.Count; i++)
            {
                DataRow dr = syncitem.SyncTb.NewRow();
                {
                    dr["StName"] = lstTables[i];
                    dr["IsSync"] = false;
                    dr["Column"] = GetColumns(strCon, lstTables[i]);
                }
                syncitem.SyncTb.Rows.Add(dr);
            }
            #endregion
        }


        //dtColumn.Rows.Count;DataTable dtColumn = ; //SyncItem.CreateColumnTable();

        //直接绑定数据
        //{
        //    DataRow drColumn = dtColumn.NewRow();
        //    { 
        //        drColumn["ScName"]="";
        //        drColumn["ScPkey"]="";
        //    }
        //    dtColumn.Rows.Add(drColumn);
        //}


        // 默认最大递归次数
        private static readonly int iMaxBlindRecursiveCount = 100;

        //表排序递归方法
        private static void BlindSortTable(ref DataTable dt, ref List<string> lstTables, ref int iBlindRecursiveCount)
        {
            iBlindRecursiveCount++;
            if (iBlindRecursiveCount > iMaxBlindRecursiveCount)
            {
                throw new Exception("表关系递归，请检查数据表的关系配置");//
            }

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                bool isMeetConditions = true;
                string strXnameOuter = dt.Rows[i]["xoname"].ToString();
                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    string strXnameInner = dt.Rows[j]["xoname"].ToString();
                    if (strXnameOuter == strXnameInner)
                    {
                        string strRnameInner = dt.Rows[j]["roname"].ToString();
                        if (!lstTables.Contains(strRnameInner))
                        {
                            isMeetConditions = false;
                            break;
                        }
                    }
                }
                if (isMeetConditions)
                {
                    lstTables.Add(strXnameOuter);//添加到列表

                    for (int j = 0; j < dt.Rows.Count; j++)
                    {
                        string strXnameInner = dt.Rows[j]["xoname"].ToString();
                        if (strXnameOuter == strXnameInner)
                        {
                            dt.Rows.RemoveAt(j);
                            j--;
                        }
                    }
                }
            }

            //回调自己
            if (dt.Rows.Count > 0)
            {
                BlindSortTable(ref  dt, ref  lstTables, ref iBlindRecursiveCount);
            }
        }
        #endregion


        #region 获取数据库连接字符串
        private static string GetConnectionString(DataRow drSyncDb)
        {
            return string.Format("Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3};", drSyncDb["Server"], drSyncDb["Database"], drSyncDb["UserID"], Cryptogram.Decrypt(drSyncDb["Password"].ToString()));//解密数据库密码
        }
        /// <summary>
        /// 获取数据库连接字符串
        /// </summary>
        /// <param name="strServer">服务器</param>
        /// <param name="strUserID">用户名</param>
        /// <param name="strPassword">密码</param>
        /// <returns>数据库连接字符串</returns>
        private static string GetConnectionString(string strServer, string strUserID, string strPassword)
        {
            return GetConnectionString(strServer, strUserID, strPassword, null);
        }
        /// <summary>
        /// 获取数据库连接字符串
        /// </summary>
        /// <param name="strServer">服务器</param>
        /// <param name="strDatabase">数据库</param>
        /// <param name="strUserID">用户名</param>
        /// <param name="strPassword">密码</param>
        /// <returns>数据库连接字符串</returns>
        private static string GetConnectionString(string strServer, string strUserID, string strPassword, string strDatabase)
        {
            //　默认连接到Master数据库
            if (string.IsNullOrEmpty(strDatabase))
            {
                strDatabase = "Master";
            }
            return string.Format("Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3};", strServer, strDatabase, strUserID, Cryptogram.Decrypt(strPassword));//解密数据库密码
        }

        #endregion 获取数据库连接字符串

        #region 备用代码
        //ggz:测试数据库权限
        //public static bool TestDbPermission(string strServer, string strUserID, string strPassword, string strDatabase, ref string strMsg)
        //{
        //    StringBuilder stbSql = new StringBuilder();
        //    {
        //        string strTestTableName = "Synchro_Test_Table_" + System.Guid.NewGuid().ToString();
        //        stbSql.Append(" create table [dbo].[");
        //        stbSql.Append(strTestTableName);
        //        stbSql.Append("](id int)");
        //        stbSql.Append(" insert [dbo].[");
        //        stbSql.Append(strTestTableName);
        //        stbSql.Append("]values(0)");
        //        stbSql.Append(" update [dbo].[");
        //        stbSql.Append(strTestTableName);
        //        stbSql.Append("] set id=1");
        //        stbSql.Append(" delete from [dbo].[");
        //        stbSql.Append(strTestTableName);
        //        stbSql.Append("]");
        //        stbSql.Append(" drop table dbo.[");
        //        stbSql.Append(strTestTableName);
        //        stbSql.Append("]");
        //    }

        //    //获取数据库连接字符串
        //    StringBuilder stbCon = new StringBuilder();
        //    stbCon.Append("server=");
        //    stbCon.Append(strServer);
        //    stbCon.Append(";database=");
        //    stbCon.Append(strDatabase);
        //    stbCon.Append(";uid=");
        //    stbCon.Append(strUserID);
        //    stbCon.Append(";pwd=");
        //    stbCon.Append(strPassword);

        //    try
        //    {
        //        using (SqlConnection con = new SqlConnection(stbCon.ToString()))
        //        {
        //            SqlCommand cmd = new SqlCommand();
        //            cmd.Connection = con;
        //            cmd.CommandText = stbSql.ToString();
        //            cmd.CommandType = CommandType.Text;
        //            cmd.Connection.Open();
        //            cmd.ExecuteNonQuery();
        //            return true;
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        //操作异常 则权限不够
        //        strMsg = ex.Message;
        //        return false;
        //    }
        //}



        //private static DataTable GetColumns(string strServer, string strUserID, string strPassword, string strDatabase, string strTable)
        //{
        //    StringBuilder stbSql = new StringBuilder();
        //    {
        //          throw new Exception();
        //    }

        //    DataTable dtColumn = new DataTable();
        //    SqlDataAdapter dad = null;
        //    string strCon = SqlHelper.GetConnectionString(strServer, strUserID, strPassword, strDatabase);
        //    try
        //    {
        //        dad = new SqlDataAdapter(stbSql.ToString(), strCon);
        //        dad.Fill(dtColumn);
        //    }
        //    catch (Exception e)
        //    {
        //        throw e;
        //    }
        //    finally
        //    {
        //        dad.Dispose();
        //    }
        //    return dtColumn;
        //}
        #endregion

        #endregion

        #region 消息 委托 事件

        public delegate void UpdatePromptDelegate(string format, params object[] args);
        public static event UpdatePromptDelegate UpdatePromptEvent;
        public static void UP(string format, params object[] args)
        {
            if (ShowMessageEvent != null)
            {
                UpdatePromptEvent(format, args);
            }
        }

        public delegate void ShowMessageDelegate(string format, params object[] args);
        public static event ShowMessageDelegate ShowMessageEvent;
        public static void SM(string format, params object[] args)
        {
            if (ShowMessageEvent != null)
            {
                ShowMessageEvent(format, args);
            }
        }
        public static void SM(SyncItem syncitem, int iDbase, int iTable, bool isSync, int iCountInsert, int iCountUpdate, int iCountDelete)
        {
            if (ShowMessageEvent != null)
            {
                if (isSync)
                {
                    ShowMessageEvent("同步信息:[{0}],[{1}].[{2}].dbo.[{3}],插入:{4}条,更新:{5}条,删除:{6}条"
                        , syncitem.SgName
                        , syncitem.SyncDb.Rows[iDbase]["Server"]
                        , syncitem.SyncDb.Rows[iDbase]["Database"]
                        , syncitem.SyncTb.Rows[iTable]["StName"]
                         , iCountInsert
                        , iCountUpdate
                        , iCountDelete);
                }
                else
                {
                    ShowMessageEvent("清理信息:[{0}],[{1}].[{2}].dbo.[{3}],插入:{4}条,更新:{5}条,删除:{6}条"
                           , syncitem.SgName
                           , syncitem.SyncDb.Rows[iDbase]["Server"]
                           , syncitem.SyncDb.Rows[iDbase]["Database"]
                           , syncitem.SyncTb.Rows[iTable]["StName"]
                            , iCountInsert
                           , iCountUpdate
                           , iCountDelete);
                }
            }
        }

        public static void SMS(SyncItem syncitem, int iDbase, int iTable, int iCountInsert, int iCountUpdate, int iCountDelete)
        {
            SM(syncitem, iDbase, iTable, true, iCountInsert, iCountUpdate, iCountDelete);
        }
        public static void SMC(SyncItem syncitem, int iDbase, int iTable, int iCountInsert, int iCountUpdate, int iCountDelete)
        {
            SM(syncitem, iDbase, iTable, false, iCountInsert, iCountUpdate, iCountDelete);
        }
        //public static void SMC(SyncItem syncitem, int iDbase, int iTable, int iCount)
        //{
        //    ShowMessageEvent("清理信息:[{0}],[{1}].[{2}].dbo.[{3}],清理过期:{4}条"
        //                       , syncitem.SgName
        //                       , syncitem.SyncDb.Rows[iDbase]["Server"]
        //                       , syncitem.SyncDb.Rows[iDbase]["Database"]
        //                       , syncitem.SyncTb.Rows[iTable]["StName"]
        //                       , iCount);
        //}
        //// 返回operation对应的中文含义
        //public static string GOCN(string operation)
        //{
        //    switch (operation)
        //    {
        //        case "0": return "过期";
        //        case "1": return "删除";
        //        case "2": return "插入";
        //        case "3": return "更新";
        //        case "4": return "更新";
        //        default:  return "未知";
        //    }
        //}

        #endregion
    }
}

////二进制的数据类型binary,image,varbinary
//if (strCoType != "binary" && strCoType != "varbinary" && strCoType != "image")